-- 包材基础信息表
CREATE TABLE PackageMaterial (
    Id INT IDENTITY(1,1) PRIMARY KEY,                -- 自增主键
    Code VARCHAR(20) NOT NULL UNIQUE,                -- 包材编码
    Type VARCHAR(50) NOT NULL,                       -- 包材类型
    Description NVARCHAR(200),                       -- 描述
    Unit VARCHAR(20) NOT NULL,                       -- 单位
    CurrentStock INT DEFAULT 0,                      -- 当前库存
    IsDeleted BIT DEFAULT 0,                         -- 是否删除标记
    CreateTime DATETIME DEFAULT GETDATE(),           -- 创建时间
    CreateBy VARCHAR(50),                            -- 创建人
    UpdateTime DATETIME,                             -- 更新时间
    UpdateBy VARCHAR(50),                            -- 更新人
    CONSTRAINT CK_CurrentStock CHECK (CurrentStock >= 0)  -- 确保库存不为负数
)
SELECT * FROM PackageMaterial
SELECT * FROM StorageRecord
-- 创建索引
CREATE INDEX IDX_PackageMaterial_Code ON PackageMaterial(Code)

-- 出入库记录表
CREATE TABLE StorageRecord (
    Id INT IDENTITY(1,1) PRIMARY KEY,                -- 自增主键
    StorageNumber VARCHAR(50),                       -- 入库单号（出库时可为空）
    MaterialCode VARCHAR(20) NOT NULL,               -- 包材编码
    Quantity INT NOT NULL,                           -- 数量
    StorageType CHAR(1) NOT NULL,                   -- 出入库类型：I-入库，O-出库
    Accumulation INT NOT NULL,                       -- 积存数量（操作后的库存）
    Remark NVARCHAR(200),                           -- 备注
    CreateTime DATETIME DEFAULT GETDATE(),           -- 创建时间
    CreateBy VARCHAR(50),                            -- 操作人
    CONSTRAINT FK_StorageRecord_Material FOREIGN KEY (MaterialCode) 
        REFERENCES PackageMaterial(Code),
    CONSTRAINT CK_StorageRecord_Quantity CHECK (Quantity > 0),  -- 确保数量为正数
    CONSTRAINT CK_StorageRecord_Type CHECK (StorageType IN ('I', 'O')),  -- 确保类型只能是I或O
    CONSTRAINT CK_StorageRecord_Accumulation CHECK (Accumulation >= 0)  -- 确保积存不为负数
)

-- 创建索引
CREATE INDEX IDX_StorageRecord_StorageNumber ON StorageRecord(StorageNumber)
CREATE INDEX IDX_StorageRecord_MaterialCode ON StorageRecord(MaterialCode)
CREATE INDEX IDX_StorageRecord_CreateTime ON StorageRecord(CreateTime)
CREATE INDEX IDX_StorageRecord_StorageType ON StorageRecord(StorageType)

-- 库存更新触发器
GO
CREATE OR ALTER TRIGGER TRG_StorageRecord_UpdateStock
ON StorageRecord
INSTEAD OF INSERT
AS
BEGIN
    -- 首先检查是否有出库操作会导致库存不足
    IF EXISTS (
        SELECT 1 
        FROM inserted i 
        JOIN PackageMaterial pm ON pm.Code = i.MaterialCode
        WHERE i.StorageType = 'O' 
        AND pm.CurrentStock < i.Quantity
    )
    BEGIN
        RAISERROR ('库存不足，无法出库', 16, 1)
        ROLLBACK TRANSACTION
        RETURN
    END

    -- 计算并插入记录（包含积存值）
    INSERT INTO StorageRecord (
        StorageNumber,
        MaterialCode,
        Quantity,
        StorageType,
        Accumulation,
        Remark,
        CreateBy
    )
    SELECT 
        i.StorageNumber,
        i.MaterialCode,
        i.Quantity,
        i.StorageType,
        CASE 
            WHEN i.StorageType = 'I' THEN pm.CurrentStock + i.Quantity
            ELSE pm.CurrentStock - i.Quantity
        END as Accumulation,
        i.Remark,
        i.CreateBy
    FROM inserted i
    JOIN PackageMaterial pm ON pm.Code = i.MaterialCode;

    -- 更新包材表的当前库存
    UPDATE pm
    SET pm.CurrentStock = CASE 
            WHEN i.StorageType = 'I' THEN pm.CurrentStock + i.Quantity
            WHEN i.StorageType = 'O' THEN pm.CurrentStock - i.Quantity
        END,
        pm.UpdateTime = GETDATE()
    FROM PackageMaterial pm
    INNER JOIN inserted i ON pm.Code = i.MaterialCode
END